﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using QuanLyKhachSan.BusinessObject;
using QuanLyKhachSan.DataAccess;
namespace QuanLyKhachSan.BusinessLogic
{
    class PhieuThuePhongBL
    {
        ConnectData connData = new ConnectData();
        public ChiTietDVBO ctdvBO = new ChiTietDVBO();
        //Lay tien dich vu
        public static int LayTienDichVu(string[,] maDichVu) {
            int tien = 0;
            ConnectData connData1 = new ConnectData();
            SqlConnection conn = connData1.Connect1();           
            for (int i = 0; i < 100; i++)
            {
                conn.Open();
                SqlCommand objCmd = new SqlCommand("select DonGia from DichVu where MaDichVu='" + maDichVu[i, 0] + "'", conn);
                SqlDataReader dr = objCmd.ExecuteReader();
                while (dr.Read())
                {
                    tien = tien + Int32.Parse(dr.GetValue(0).ToString()) * Int32.Parse(maDichVu[i,1]);
                }
                conn.Close();
            }
            return tien;
        }



        //Tim kiem
        public DataTable TimKiemPhieuThuePhong(string timKiem)
        {
            string sql = "select * from PhieuThuePhong where MaThuePhong like '%" + timKiem + "%' or NgayThue like '%" + timKiem + "%'";
            return connData.GetDataTable(sql);
        }

        //lấy danh sách thuê phòng
        public DataTable LayDSThuePhong() {
            string sql = "LayDSThuePhong";
            return connData.GetDataTable(sql);
        }

        //lấy danh sách phòng chưa thanh toán
        public DataTable LayDSPhongChuaThanhToan()
        {
            string sql = "LayDSPhongChuaThanhToan";
            return connData.GetDataTable(sql);
        }

        //lấy danh sach tình trạng thanh toán
        public DataTable LayTinhTrangThanhToan()
        {
            string sql = "select distinct TinhTrang,"
                + "TTThanhToan = case TinhTrang when 0 then N'Chưa Thanh Toán'"
                + "when 1 then N'Đã Thanh Toán' end from ThuePhong";
            return connData.GetDataTable(sql);
        }

        //thêm mới khách hàng
        public void ThemPTP(PhieuThuePhongBO ptpBO)
        {
            //string sql = string.Format("INSERT INTO ThuePhong (MaThuePhong, MaNhanVien, MaKhachHang, MaPhong, NgayThue, NgayTra, TinhTrang, TienPhong) "
            //     + "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", ptpBO.MaThuePhong, ptpBO.MaNhanVien, ptpBO.MaKhachHang, ptpBO.MaPhong, ptpBO.NgayThue.ToString("MM-dd-yyyy"), ptpBO.NgayTra.ToString("MM-dd-yyyy"), ptpBO.TinhTrang, ptpBO.TienPhong);
            //if (connData.ExeCuteQuery(sql))
            //{
            //    MessageBox.Show("Thêm mới phiếu thuê phòng thành công!", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "ThemMoiThuePhong";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaThuePhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaNhanVien", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaKhachHang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@NgayThue", SqlDbType.DateTime);
            objCmd.Parameters.Add("@NgayTra", SqlDbType.DateTime);
            objCmd.Parameters.Add("@TinhTrang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TienPhong", SqlDbType.Int);

            objCmd.Parameters["@MaThuePhong"].Value = ptpBO.MaThuePhong;
            objCmd.Parameters["@MaNhanVien"].Value = ptpBO.MaNhanVien;
            objCmd.Parameters["@MaKhachHang"].Value = ptpBO.MaKhachHang;
            objCmd.Parameters["@MaPhong"].Value = ptpBO.MaPhong;
            objCmd.Parameters["@NgayThue"].Value = ptpBO.NgayThue;
            objCmd.Parameters["@NgayTra"].Value = ptpBO.NgayTra;
            objCmd.Parameters["@TinhTrang"].Value = ptpBO.TinhTrang;
            objCmd.Parameters["@TienPhong"].Value = ptpBO.TienPhong;
            objCmd.ExecuteNonQuery();
            conn.Close();

        }

        //
        public string NextID()
        {
            return Utilities.NextID(connData.GetLastID("ThuePhong", "MaThuePhong"), "TP");
        }

        public bool CheckExists(string matp)
        {
            if (connData.CheckExistValue("ThuePhong", "MaThuePhong", matp))
            {
                return true;
            } return false;
        }

        public void SuaPTP(PhieuThuePhongBO ptpBO)
        {
            //string sql = string.Format("UPDATE ThuePhong SET MaNhanVien = '{0}', MaKhachHang = '{1}', "
            //                + "MaPhong = '{2}', NgayThue = '{3}', NgayTra = '{4}', TinhTrang = '{5}', TienPhong = '{6}' WHERE MaThuePhong = '{7}' ",
            //                         ptpBO.MaNhanVien, ptpBO.MaKhachHang, ptpBO.MaPhong, ptpBO.NgayThue.ToString("MM-dd-yyyy"), ptpBO.NgayTra.ToString("MM-dd-yyyy"), ptpBO.TinhTrang, ptpBO.TienPhong, ptpBO.MaThuePhong);
            //if (connData.ExeCuteQuery(sql))
            //{
            //    MessageBox.Show("Sửa phiếu thuê phòng thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //}
            SqlConnection conn = connData.Connect1();
            conn.Open();
            string sql = "SuaThuePhong";
            SqlCommand objCmd = new SqlCommand(sql, conn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add("@MaThuePhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaNhanVien", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaKhachHang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@MaPhong", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@NgayThue", SqlDbType.DateTime);
            objCmd.Parameters.Add("@NgayTra", SqlDbType.DateTime);
            objCmd.Parameters.Add("@TinhTrang", SqlDbType.NVarChar, 30);
            objCmd.Parameters.Add("@TienPhong", SqlDbType.Int);

            objCmd.Parameters["@MaThuePhong"].Value = ptpBO.MaThuePhong;
            objCmd.Parameters["@MaNhanVien"].Value = ptpBO.MaNhanVien;
            objCmd.Parameters["@MaKhachHang"].Value = ptpBO.MaKhachHang;
            objCmd.Parameters["@MaPhong"].Value = ptpBO.MaPhong;
            objCmd.Parameters["@NgayThue"].Value = ptpBO.NgayThue;
            objCmd.Parameters["@NgayTra"].Value = ptpBO.NgayTra;
            objCmd.Parameters["@TinhTrang"].Value = ptpBO.TinhTrang;
            objCmd.Parameters["@TienPhong"].Value = ptpBO.TienPhong;
            objCmd.ExecuteNonQuery();
            conn.Close();
        }

        public void ThanhToan(PhieuThuePhongBO ptpBO)
        {
            string sql = string.Format("UPDATE ThuePhong SET MaNhanVien = '{0}', MaKhachHang = '{1}', "
                            + "MaPhong = '{2}', NgayThue = '{3}', NgayTra = '{4}', TinhTrang = '{5}', TienPhong = '{6}' WHERE MaThuePhong = '{7}' ",
                                     ptpBO.MaNhanVien, ptpBO.MaKhachHang, ptpBO.MaPhong, ptpBO.NgayThue.ToString("MM-dd-yyyy"), ptpBO.NgayTra.ToString("MM-dd-yyyy"), ptpBO.TinhTrang, ptpBO.TienPhong, ptpBO.MaThuePhong);
            if (connData.ExeCuteQuery(sql))
            {
                MessageBox.Show("Thanh Toán phòng thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        //xóa
        public void Delete(string ptp)
        {
            string sql = "Delete from ThuePhong where MaThuePhong='" + ptp + "'";
            connData.ExeCuteQuery(sql);
        }
    }
}
